﻿using Azure;
using Dapper;
using HrEFCore;
using HrEFCore.Models;
using HrIService;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Distributed;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HrService
{
	public class ERService : IERService
	{

		private readonly MyDbContext myDbContext;
        

        public ERService(MyDbContext myDbConext)
		{
			
			this.myDbContext = myDbConext;
		}

        public async Task<int> AddEIAsync(EI ei)
        {
            myDbContext.EI.AddAsync(ei);
            return await myDbContext.SaveChangesAsync();
        }

        //添加
        public async Task<int> AddERAsync(ER er)
		{
			//myDbContext.ER.AddAsync(er);
			//return await myDbContext.SaveChangesAsync();
			using (var conn = myDbContext.Database.GetDbConnection())
			{
				DynamicParameters dp = new DynamicParameters();
				dp.Add("@HumanMajorKindId", er.HumanMajorKindId);
				dp.Add("@HumanMajorKindName", er.HumanMajorKindName);
				dp.Add("@HumanMajorId", er.HumanMajorId);
				dp.Add("@HunmaMajorName", er.HunmaMajorName);
				dp.Add("@EngageType", er.EngageType);
				dp.Add("@HumanPicture", er.HumanPicture);
				dp.Add("@HumanName", er.HumanName);
				dp.Add("@HumanSex", er.HumanSex);
				dp.Add("@HumanEmail", er.HumanEmail);
				dp.Add("@HumanTelephone", er.HumanTelephone);
				dp.Add("@HumanHomephone", er.HumanHomephone);
				dp.Add("@HumanMobilephone", er.HumanMobilephone);
				dp.Add("@HumanAddress", er.HumanAddress);
				dp.Add("@HumanPostcode", er.HumanPostcode);
				dp.Add("@HumanNationality", er.HumanNationality);
				dp.Add("@HumanBirthplace", er.HumanBirthplace);
				dp.Add("@HumanBirthday", er.HumanBirthday);
				dp.Add("@HumanRace", er.HumanRace);
				dp.Add("@HumanReligion", er.HumanReligion);
				dp.Add("@HumanParty", er.HumanParty);
				dp.Add("@HumanIdcard", er.HumanIdcard);
				dp.Add("@HumanAge", er.HumanAge);
				dp.Add("@HumanCollege", er.HumanCollege);
				dp.Add("@HumanEducatedDegree", er.HumanEducatedDegree);
				dp.Add("@HumanEducatedYears", er.HumanEducatedYears);
				dp.Add("@HumanEducatedMajor", er.HumanEducatedMajor);
				dp.Add("@DemandSalaryStandard", er.DemandSalaryStandard);
				dp.Add("@RegistTime", er.RegistTime);
				dp.Add("@HumanSpeciality", er.HumanSpeciality);
				dp.Add("@HumanHobby", er.HumanHobby);
				dp.Add("@HumanHistoryRecords", er.HumanHistoryRecords);
				dp.Add("@Remark", er.Remark);
				dp.Add("@Register", er.Register);
				string sql = "exec [dbo].[ERAdd4]  @HumanMajorKindId, @HumanMajorKindName, @HumanMajorId, @HunmaMajorName, @EngageType, @HumanPicture, @HumanName, @HumanSex, @HumanEmail, @HumanTelephone, @HumanHomephone, @HumanMobilephone, @HumanAddress, @HumanPostcode, @HumanNationality, @HumanBirthplace, @HumanBirthday, @HumanRace, @HumanReligion, @HumanParty, @HumanIdcard, @HumanAge, @HumanCollege, @HumanEducatedDegree, @HumanEducatedYears, @HumanEducatedMajor, @DemandSalaryStandard, @RegistTime, @HumanSpeciality, @HumanHobby, @HumanHistoryRecords, @Remark,@Register";
				int row = conn.Execute(sql, dp);
				if (row > 0)
				{
					return row;
				}
				else
				{
					return row;
				}
			}
		}

		//三级机构下拉
		public async Task<CFTK> cftkById(string id)
		{
			return await myDbContext.CFTK.Where(e => e.ThirdKindId == id).FirstOrDefaultAsync();
		}

        public async Task<ERCheckIn> CheckIn(short resId)
        {
            //面试结果登记中的ER表的数据
            var eRCheckIn = await myDbContext.ERCheckIn.FirstOrDefaultAsync(e => e.ResId == resId);
            return eRCheckIn;
        }

        public List<ER> ERCXresId(short resId)
        {
            // 根据resId查询ER所有数据的逻辑
            var erList = myDbContext.ER.Where(e => e.ResId == resId).ToList();
            return erList;
        }

		public async Task<ERHiredApproval> ERHiredApproval(short resId)
		{
			//录用管理--录用审批中的审批页面
			var eRHiredApproval = await myDbContext.ERHiredApproval.FirstOrDefaultAsync(e => e.ResId == resId);
			return eRHiredApproval;
		}

		public async Task<ERHiredApplication> ERHiredApplicationEI(short resId)
		{
			//录用管理--录用申请中的申请页面
			var eRHiredApplication = await myDbContext.ERHiredApplication.FirstOrDefaultAsync(e => e.ResId == resId);
			return eRHiredApplication;
		}

		public async Task<ERSift> ERSiftEI(short resId)
		{
			//面试筛选中的ER表和EI表的数据
			var eRSift = await myDbContext.ERSift.FirstOrDefaultAsync(e => e.ResId == resId);
			return eRSift;
		}

		//根据  姓名，电话，身份证号码，个人履历  进行模糊查询
		public IEnumerable<ER> GetERByCondition(string condition)
        {
            return myDbContext.ER.Where(e =>
           e.HumanName.Contains(condition) ||
           e.HumanTelephone.Contains(condition) ||
           e.HumanIdcard.Contains(condition) ||
           e.HumanHistoryRecords.Contains(condition));
        }

        public List<ERView> GetPagedERView(int page, int pageSize)
        {
			//面试管理--面试结果登记分页
			var sql = "SELECT ResId,HumanName,HumanSex, HumanMajorKindName, HunmaMajorName, HumanTelephone,InterviewStatus FROM [dbo].[ERView]";
            var queryResult = myDbContext.ERView.FromSqlRaw(sql).Skip((page - 1) * pageSize).Take(pageSize).ToList();

            // 对查询结果进行空值检查
            var result = queryResult.Where(x => x != null).ToList();

			return result;
        }

		public List<ERView2> GetPagedERView2(int page, int pageSize)
		{
			//面试管理--面试筛选分页
			var sql = "SELECT ResId,HumanName,HumanSex, HumanMajorKindName, HunmaMajorName, HumanTelephone,InterviewStatus,MultiQualityDegree,RegisteTime,InterviewAmount FROM [dbo].[ERView2] where InterviewStatus > 1";
			var queryResult = myDbContext.ERView2.FromSqlRaw(sql).Skip((page - 1) * pageSize).Take(pageSize).ToList();

			// 对查询结果进行空值检查
			var result = queryResult.Where(x => x != null).ToList();

			return result;
		}

		public List<ERHired> Hired(int page, int pageSize)
		{
			//录用管理中的录用申请的ER表中的数据
			var sql = "SELECT ResId,HumanName,HumanMajorKindName,HunmaMajorName,HumanSex,HumanAge,HumanEducatedMajor,HumanCollege,InterviewStatus FROM [dbo].[ERHired] where InterviewStatus > 1";
			var queryResult = myDbContext.ERHired.FromSqlRaw(sql).Skip((page - 1) * pageSize).Take(pageSize).ToList();

			// 对查询结果进行空值检查
			var result = queryResult.Where(x => x != null).ToList();

			return result;
		}
		public List<ERHired2> Hired2(int page, int pageSize)
		{
			//录用管理中的录用申请的ER表中的数据
			var sql = "SELECT ResId,HumanName,HumanMajorKindName,HunmaMajorName,HumanSex,HumanAge,HumanEducatedMajor,HumanCollege,PassCheckComment FROM [dbo].[ERHired2] where PassCheckComment = '通过'";
			var queryResult = myDbContext.ERHired2.FromSqlRaw(sql).Skip((page - 1) * pageSize).Take(pageSize).ToList();

			// 对查询结果进行空值检查
			var result = queryResult.Where(x => x != null).ToList();

			return result;
		}
		public List<ERHired3> Hired3(int page, int pageSize)
		{
			//录用管理中的录用申请的ER表中的数据
			var sql = "SELECT ResId,HumanName,HumanMajorKindName,HunmaMajorName,HumanSex,HumanAge,HumanEducatedMajor,HumanCollege,PassCheckComment,PassPassComment FROM [dbo].[ERHired3] WHERE PassCheckComment = '通过' AND PassPassComment = '通过' ";
			var queryResult = myDbContext.ERHired3.FromSqlRaw(sql).Skip((page - 1) * pageSize).Take(pageSize).ToList();

			// 对查询结果进行空值检查
			var result = queryResult.Where(x => x != null).ToList();

			return result;
		}

		public async Task<int> Revise(short resId, string passCheckComment, string attachmentName, string checker)
		{
			//根据resId修改ER中的PassCheckComment和AttachmentName和HumanPicture和checker数据
			var sql = $"UPDATE [dbo].[ER] SET PassCheckComment = '{passCheckComment}',AttachmentName='{attachmentName}',Checker='{checker}' WHERE [ResId] = '{resId}'";
			return await myDbContext.Database.ExecuteSqlRawAsync(sql);
		}

		public async Task<int> ReviseInterviewStatus(short resId, short interviewStatus)
		{
			// 根据ResId修改ER中的InterviewStatus(面试状态)数据
			var sql = $"UPDATE [dbo].[ER] SET InterviewStatus = '{interviewStatus}' WHERE [ResId] = '{resId}'";
			return await myDbContext.Database.ExecuteSqlRawAsync(sql);
		}

		public async Task<int> RevisePassCheckComment(short resId, string passCheckComment)
		{
			//根据resId修改ER中的PassCheckComment数据 
			var sql = $"UPDATE [dbo].[ER] SET PassCheckComment = '{passCheckComment}' WHERE [ResId] = '{resId}'";
			return await myDbContext.Database.ExecuteSqlRawAsync(sql);
		}
		public async Task<int> RevisePassPassComment(short resId, string passPassComment)
		{
			//根据resId修改ER中的PassCheckComment数据 
			var sql = $"UPDATE [dbo].[ER] SET PassPassComment = '{passPassComment}' WHERE [ResId] = '{resId}'";
			return await myDbContext.Database.ExecuteSqlRawAsync(sql);
		}
		public async Task<CM> SelectCMById(string id)
		{
			return await myDbContext.CM.FirstOrDefaultAsync(e => e.MajorId == id);
		}
		//职位名称
		public async Task<CMK> SelectCMKById(string id)
		{
			return await myDbContext.CMK.FirstOrDefaultAsync(e => e.MajorKindId == id);
		}
		//薪酬
		public async Task<SSD> SelectSSDById(string id)
		{ 
			return await myDbContext.SSD.FirstOrDefaultAsync(e => e.StandardId == id);
		}

		public async Task<ERHiredInquire> ERHiredInquire(short resId)
		{
			//录用管理--录用查询中的查询页面		
			var eRHiredInquire = await myDbContext.ERHiredInquire.FirstOrDefaultAsync(e => e.ResId == resId);
			return eRHiredInquire;
		}
		//根据Id查询数据
		public async Task<ER> SelectERById(short id)
		{
			return await myDbContext.ER.FirstOrDefaultAsync(e => e.ResId == id);
			//return await myDbContext.ER.Where(e => e.ResId == id).FirstOrDefaultAsync;

		}
		//推荐中修改的字段
		public async Task<int> Update(ER er)
		{
			var entity = myDbContext.ER.Attach(er);
			//entity.Property(e => e.InterviewStatus).IsModified = true;
			entity.Property(e => e.CheckStatus).IsModified = true;//状态
			entity.Property(e => e.Recomandation).IsModified = true;//推荐意见
			entity.Property(e => e.PassChecker).IsModified = true;//通过复核人
			entity.Property(e => e.PassCheckTime).IsModified = true;//通过复核时间
			return await myDbContext.SaveChangesAsync();
		}
		public async Task<FenYe2<ER>> SelectAllFYERAsync(FenYe2<ER> fenYe)
		{
			using (var conn = myDbContext.Database.GetDbConnection())
			{
				DynamicParameters dp = new DynamicParameters();
				dp.Add("@currentPage", fenYe.CurrentPage);
				dp.Add("@pageSize", fenYe.PageSize);
				dp.Add("@keyName", "ResId");
				dp.Add("@tableName", "ER");
				dp.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);
				string sql = "exec [dbo].[proc_FenYe] @currentPage,@pageSize,@keyName,@tableName,@rows out";
				fenYe.List = await conn.QueryAsync<ER>(sql, dp);
				fenYe.Rows = dp.Get<int>("rows");
				return fenYe;
			}
		}
		public async Task<List<CM>> SelectNameLD(string id)//职业分类找职业名称
		{
			return await myDbContext.CM.Where(e => e.MajorKindId == id).ToListAsync();
		}
		public async Task<FenYe2<ER>> SelectFYTJER(int CurrentPage, int PageSize, string where)
		{
			using (var conn = myDbContext.Database.GetDbConnection())
			{
				DynamicParameters dp = new DynamicParameters();
				dp.Add("@pageSize", PageSize);
				dp.Add("@keyName", "ResId");
				dp.Add("@tableName", "ER");
				dp.Add("@where", where);
				dp.Add("@currentPage", CurrentPage);
				dp.Add("@lie", "*");
				dp.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);
				string sql = "exec [dbo].[proc_FenYe2] @pageSize,@keyName,@tableName,@where,@currentPage,@lie,@rows out";
				IEnumerable<ER> list = await conn.QueryAsync<ER>(sql, dp);
				int rows = dp.Get<int>("rows");
				FenYe2<ER> fy = new FenYe2<ER>()
				{
					List = list,
					Rows = rows
				};
				return fy;
			}
		}

		public async Task<EMR> EMRById(short MreId)
		{
			return await myDbContext.EMR.FirstOrDefaultAsync(e=>e.MreId == MreId);
		}

		public async Task<ER> ERById(short id)
		{
			return await myDbContext.ER.FirstOrDefaultAsync(e=>e.ResId == id);
		}
	}
}
